package com.hctforgreen.greenservice.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.hctforgreen.greenservice.utils.HctConstants;

/**
 * DB底层操作
 * 
 * @author Tallen Zhai
 * 
 */
public class DbAdapter {
	private static final String DATABASE_NAME = HctConstants.DB_NAME;

	private static final int DBVERSION = HctConstants.DB_VERSION;
	private Context mContext;
	private DBHelper mHelper;
	private SQLiteDatabase mDatabase;
	/**
	 * 创建series(系列信息)SQL
	 */
	private static final String CREATE_SERIES_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_SERIE_LST
			+ " (_id integer primary key autoincrement, "
			+ "id text, "
			+ "seriesTypeName text," + "name text" + ");";
	/**
	 * 创建machines(机型信息)SQL
	 */
	private static final String CREATE_MACHINE_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_MACHINE_LST
			+ " (_id integer primary key autoincrement, "
			+ "seriesId text, "
			+ "name text, "
			+ "childSeriesId text, "
			+ "barcode text, "
			+ "id text" + ");"; // 当DB_VERSION = 13时add的字段
	/**
	 * 创建book_types(资料类别信息)SQL
	 */
	private static final String CREATE_BOOK_TYPES_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_BOOK_TYPE_LST
			+ " (_id integer primary key autoincrement, "
			+ "id text, "
			+ "name text" + ");";

	/**
	 * 创建books(图书列表)SQL
	 */
	private static final String CREATE_BOOKS_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_BOOK_LST
			+ " (_id integer primary key autoincrement, "
			+ "id text, "
			+ "bookName text, "
			+ "booktypeId text, "
			+ "booktypeName text, "
			+ "seriesId text, " // 当DB_VERSION = 10时delete的字段，
			+ "bookFileSize text, "
			+ "versionCode text, "
			+ "download_url text, "
			+ "childSeriesId text, " // 当DB_VERSION = 10时delete的字段，
			+ "bookZipState text, "
			+ "progress integer, "
			+ "bCanUpdate text, " + "childSeriesName text, " // 当DB_VERSION =
																// 10时delete的字段，
			+ "doWhatClick text, " // 当DB_VERSION = 9时add的字段，用来区分用户是“下载”还是“更新”
			+ "column text, " // 当DB_VERSION = 10时add的字段,用来区分是“技术资料”还是“调试维修”
			+ "suffix text,"// 新字段 文件后缀名
			+ "displayName text, " // 当DB_VERSION = 10时add的字段
			+ "bookFileLongSize text, " // 当DB_VERSION = 10时add的字段
			+ "currentBookFileLongSize text, " // 当DB_VERSION = 10时add的字段 老的
			+ "currentBookId text" + ");"; // 当DB_VERSION = 10时add的字段 老的

	/**
	 * 创建machineBooks SQL
	 */
	private static final String CREATE_MACHINE_BOOK_LST_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_MACHINE_BOOK_LST
			+ " (_id integer primary key autoincrement, "
			+ "machineId text, "
			+ "machineBookIdList text" + ");";
	/**
	 * 创建childSeriesBooks SQL
	 */
	private static final String CREATE_CHILD_SERIES_BOOK_LST_TABLE_SQL = "create table IF NOT EXISTS "
			+ HctConstants.DB_TABLE_NAME_CHILD_SERIES_BOOK_LST
			+ " (_id integer primary key autoincrement, "
			+ "childSeriesId text, "
			+ "childSeriesName text, "
			+ "seriesId text, " + "childSeriesBookIdList text" + ");";

	/**
	 * 构造函数
	 * 
	 * @param context
	 */
	public DbAdapter(Context context) {
		mContext = context;
		mHelper = new DBHelper(mContext);
		mDatabase = mHelper.getWritableDatabase();
	}

	private class DBHelper extends SQLiteOpenHelper {

		private static final String CREATE_TEMP_BOOKS_TABLE_SQL = "alter table "
				+ HctConstants.DB_TABLE_NAME_BOOK_LST
				+ " rename to "
				+ HctConstants.DB_TABLE_NAME_TEMP_BOOK_LST;

		private static final String INSERT_TEMP_BOOKS_TABLE_DATA_2_BOOKS_TABLE_DB_VERSION_9_SQL = "insert into "
				+ HctConstants.DB_TABLE_NAME_BOOK_LST + " select *,''"// ''是为新增的字段插入空值
				+ " from " + HctConstants.DB_TABLE_NAME_TEMP_BOOK_LST;
		private static final String INSERT_TEMP_BOOKS_TABLE_DATA_2_BOOKS_TABLE_DB_VERSION_12_SQL = "insert into "
				+ HctConstants.DB_TABLE_NAME_BOOK_LST + " select *,'','0','0'"// ''是为新增的字段插入空值
				+ " from " + HctConstants.DB_TABLE_NAME_TEMP_BOOK_LST;
		private String DROP_TEMP_BOOKS_TABLE_SQL = "DROP TABLE IF EXISTS "
				+ HctConstants.DB_TABLE_NAME_TEMP_BOOK_LST;

		public DBHelper(Context context) {
			super(context, DATABASE_NAME, null, DBVERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			/**
			 * 创建newVersion
			 */
			db.execSQL(CREATE_SERIES_TABLE_SQL);
			db.execSQL(CREATE_MACHINE_TABLE_SQL);
			db.execSQL(CREATE_BOOK_TYPES_TABLE_SQL);
			// db.execSQL(CREATE_ENTRY_TITLE_LST_TABLE_SQL);
			// db.execSQL(CREATE_ENTRY_LST_TABLE_SQL);
			db.execSQL(CREATE_BOOKS_TABLE_SQL);
			db.execSQL(CREATE_MACHINE_BOOK_LST_TABLE_SQL);
			db.execSQL(CREATE_CHILD_SERIES_BOOK_LST_TABLE_SQL);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			/**
			 * drop掉oldVersion
			 */
			if (oldVersion < 11) {
				db.execSQL("DROP TABLE IF EXISTS "
						+ HctConstants.DB_TABLE_NAME_MACHINE_LST);
				db.execSQL("DROP TABLE IF EXISTS "
						+ HctConstants.DB_TABLE_NAME_BOOK_TYPE_LST);
				// db.execSQL("DROP TABLE IF EXISTS " +
				// HctConstants.DB_TABLE_NAME_ENTRY_TITLE_LST);
				// db.execSQL("DROP TABLE IF EXISTS " +
				// HctConstants.DB_TABLE_NAME_ENTRY_LST);
				db.execSQL("DROP TABLE IF EXISTS "
						+ HctConstants.DB_TABLE_NAME_BOOK_LST);

				db.execSQL("DROP TABLE IF EXISTS "
						+ HctConstants.DB_TABLE_NAME_MACHINE_BOOK_LST);
				db.execSQL("DROP TABLE IF EXISTS "
						+ HctConstants.DB_TABLE_NAME_CHILD_SERIES_BOOK_LST);
			}
			db.execSQL("DROP TABLE IF EXISTS "
					+ HctConstants.DB_TABLE_NAME_SERIE_LST);

			/**
			 * 重建db
			 */
			onCreate(db);

		}
	}

	/**
	 * 执行
	 * 
	 * @param sql
	 */
	public void execSQL(String sql) {

		try {
			mDatabase.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行
	 * 
	 * @param sql
	 */
	public void execSQL(String sql, Object[] bindArgs) {

		try {
			mDatabase.execSQL(sql, bindArgs);
		} catch (Exception e) {
		}
	}

	/**
	 * 插入
	 * 
	 * @param table
	 * @param nullColumnHack
	 * @param values
	 * @return
	 */
	public long insert(String table, String nullColumnHack, ContentValues values) {
		return mDatabase.insert(table, nullColumnHack, values);
	}

	/**
	 * 查询
	 * 
	 * @param sql
	 * @param bindArgs
	 * @return
	 */
	public Cursor rawQuery(String sql, String[] bindArgs) {
		return mDatabase.rawQuery(sql, bindArgs);
	}

	/**
	 * 关闭
	 */
	public void close() {
		mDatabase.close();
	}

	public SQLiteDatabase getDatabase() {
		return mDatabase;
	}

	public void beginTransaction() {
		mDatabase.beginTransaction();
	}

	public void setTransactionSuccessful() {
		mDatabase.setTransactionSuccessful();
	}

	public void endTransaction() {
		mDatabase.endTransaction();
	}
}
